package dataLayer;

import model.Group;
import interfaces.IDAOGroup;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.TreeSet;

public class DAOGroup extends DBconnect implements IDAOGroup {
	

	private static DAOGroup single;

	// Make SQL query and call to DB to addGroup
	public void insertGroup(Group g) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_add_group('" + g.getGroup_name() + "');";
			stmt.execute(sql);
			stmt.close();
			closeConn();
		} catch (Exception e) {
			System.err.println("SQL Error Dao Insergroup");
		}
	}

	@Override
	public void loadGroup(Group g) {

	}

	// Make SQL query and call to DB to EditGroup
	public void updateGroup(Group g) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_update_group('" + g.getGroup_name_old()
					+ "', '" + g.getGroup_name() + "')";
			stmt.execute(sql);
			stmt.close();
			closeConn();
		} catch (Exception e) {
			System.err.println("SQL Error Dao UpdateGroup");

		}

	}

	// Make SQL query and call to DB to deleteGroup
	public void deleteGroup(Group g) {
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "CALL `sp_delete_group`('" + g.getGroup_name() + "')";
			// TODO zorg dat de juiste group verwijderd wordt uit treeset
			stmt.execute(sql);
			stmt.close();
			closeConn();
		} catch (Exception e) {
			System.err.println("SQL Error Dao DeleteGroup");

		}
	}

	// Make SQL query and call to DB to loadAllGroups
	public TreeSet<Group> loadallGroup() {

		TreeSet<Group> groupset = new TreeSet<Group>();
		try {
			openConn();
			Statement stmt = getConn().createStatement();
			String sql = "call sp_load_all_groups";
			ResultSet rs = stmt.executeQuery(sql);
			groupset.clear();

			while (rs.next()) {
				Group g = new Group(null, null);
				g.setGroup_name(rs.getString("name"));

				groupset.add(g);
			}

			stmt.close();
			closeConn();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQL Error Dao LoadAllGroup");
		}
		return groupset;
	}

	public static DAOGroup getInstance() {
		if (single == null) {
			single = new DAOGroup();
		}
		return single;
	}

}
